package com.lm.activiti.boot.tools.excel;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import com.beust.jcommander.internal.Lists;
import com.lm.activiti.boot.tools.excel.common.ExportDto;
import net.coobird.thumbnailator.Thumbnails;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.*;
import org.joda.time.DateTime;
import org.junit.Test;

import javax.imageio.ImageIO;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.List;

/**
 * @author lm_jcf
 * @date 2020/1/6 14:40
 */
public class PoiTest {


    @Test
    public void buildData() throws IOException {


        String[] heads = {"序号", "请假学生", "班级", "请假类型", "请假时间", "请假事由", "发起人", "发起时间", "状态"};

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        Row row_1 = sheet.createRow(1);
        Cell cell_1_1 = row_1.createCell(1);
        Cell cell_1_2 = row_1.createCell(2);
        Cell cell_1_3 = row_1.createCell(3);

        cell_1_1.setCellValue("合并单元格的话,建议先合并,合并之后,在合并的第一行第一列set值就可以了");
        cell_1_2.setCellValue("dedededeede");
        cell_1_3.setCellValue(8949849848949489D);



        Row row_2 = sheet.createRow(2);
        Cell cell_2_1 = row_2.createCell(1);
        Cell cell_2_2 = row_2.createCell(2);
        Cell cell_2_3 = row_2.createCell(3);

        cell_2_1.setCellValue("合并单元格的话,建议先合并,合并之后,在合并的第一行第一列set值就可以了");
        cell_2_2.setCellValue("dedededeede");
        cell_2_3.setCellValue(8949849848949489D);

        // 这里代表在第0行开始,到0行结束,从0列开始,到10列结束,进行合并,也就是合并第0行的0-10个单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1,2,2,2);
        sheet.addMergedRegion(cellRangeAddress);

        // 自适应列宽
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);


        String filePath = "C:\\Users\\lm_jcf\\Desktop\\";

        File file = new File(filePath +"poi_"+ DateTime.now().getSecondOfDay() + ".xlsx");
        if (!file.exists()) {
            file.getParentFile().mkdirs();
        }

        System.out.println(file.getName());
        System.out.println(file.getPath());

        workbook.write(new FileOutputStream(file));
    }

    @Test
    public void exportPic() throws Exception {
        BigExcelWriter writer = (BigExcelWriter) ExcelUtil.getBigWriter("C:\\Users\\lm_jcf\\Desktop\\pic-excel.xlsx");

        SXSSFWorkbook wb = (SXSSFWorkbook)writer.getWorkbook();
        SXSSFDrawing drawing = (SXSSFDrawing)writer.getSheet().createDrawingPatriarch();
        // 设置位置，属性
        ClientAnchor anchor = wb.getCreationHelper().createClientAnchor();

        SXSSFSheet sheet = wb.createSheet();

        for(int i=0;i<10;i++){
            SXSSFRow row = sheet.createRow(i);

            row.setHeight((short) 800);

            row.createCell(0).setCellValue("第一块");
            row.createCell(1).setCellValue("第二块");

            if (i == 99) {
                System.out.println("测测");
            }

            InputStream inputStream = new FileInputStream(new File("C:\\Users\\lm_jcf\\Desktop\\pic.jpg"));
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            IoUtil.copy(inputStream, outputStream, IoUtil.DEFAULT_BUFFER_SIZE);

//            Thumbnails.of(inputStream).scale(0.12f).outputQuality(0.30f).toOutputStream(outputStream);
            anchor.setRow1(i+1);
            anchor.setRow2(i+2);
            anchor.setCol1(1);
            anchor.setCol2(2);
            drawing.createPicture(anchor, writer.getWorkbook().addPicture(outputStream.toByteArray(), SXSSFWorkbook.PICTURE_TYPE_PNG));
            outputStream.close();
            inputStream.close();
        }
        writer.close();
    }

    @Test
    public void exportPicExcel() throws Exception {

        File file = new File("C:\\Users\\lm_jcf\\Desktop\\pic.xlsx");

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        XSSFSheet sheet = wb.createSheet();

        for (int i = 0; i < 5; i++) {

            if (i == 2) {
                sheet.setColumnWidth(i, 13 * 256);
                continue;
            }
            sheet.setColumnWidth(i, 30 * 160);
        }

        sheet.setColumnWidth(2,12*256);


        XSSFCell cell = sheet.createRow(2).createCell(1);
        cell.setCellStyle(cellStyle);

        sheet.getRow(2).setHeightInPoints(90F);
//        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//        cell.setCellFormula("HYPERLINK(\"" + "https://file.52kiwi.cn/group11/M00/20/16/rBIAEWCeJIeEF8TvAAAAAL1Sr8s580.jpg" + "\")");

        cell.setCellValue("姓名");
        CreationHelper createHelper = wb.getCreationHelper();

        Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
        hyperlink.setLabel("姓名");
        hyperlink.setAddress("https://file.52kiwi.cn/group11/M00/20/16/rBIAEWCeJIeEF8TvAAAAAL1Sr8s580.jpg");
        cell.setHyperlink(hyperlink);

        insertNetImages(wb, 2, 2, 0L, 0L, Lists.newArrayList(
                "https://file.52kiwi.cn/group11/M00/20/16/rBIAEWCeJIeEF8TvAAAAAL1Sr8s580_150x0_rs.jpg"
        ));

        wb.write(new FileOutputStream(file));
        wb.close();

    }

    @Test
    public void teste(){
        String url = "https://file.52kiwi.cn/group11/M00/20/16/rBIAEWCeJIeEF8TvAAAAAL1Sr8s580.jpg";

        String s = url.substring(0,url.lastIndexOf(".")) + "_150x0_rs" + url.substring(url.lastIndexOf("."), url.length());

        System.out.println(s);

    }

    public static void insertNetImages(XSSFWorkbook wb, int rowIndex, int colstartIndex, float scale, float quality, List<String> imageUrls) throws Exception {
        HttpURLConnection conn = null;
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();

        for (String imageUrl : imageUrls) {
            URL url = new URL(imageUrl);
            //打开链接
            conn = (HttpURLConnection) url.openConnection();
            //设置请求方式为"GET"
            conn.setRequestMethod("GET");
            //超时响应时间为50秒
            conn.setConnectTimeout(50 * 1000);
            //通过输入流获取图片数据
            InputStream inStream = conn.getInputStream();
            //得到图片的二进制数据，以二进制封装得到数据，具有通用性 (压缩)
//            byte[] byteData = readInputStream(inStream, scale, quality);
            byte[] byteData = readInputStream(inStream);
            XSSFClientAnchor anchor =
                    new XSSFClientAnchor(0, 0, 0, 0,
                            (short) colstartIndex, rowIndex, (short) colstartIndex + 1,
                            rowIndex + 1);
            //设置图片随单元移动调整大小
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
            XSSFPicture picture = patriarch.createPicture(anchor, wb.addPicture(byteData, XSSFWorkbook.PICTURE_TYPE_JPEG));
        }
    }


    private static byte[] readInputStream(InputStream inStream) throws Exception{
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while( (len=inStream.read(buffer)) != -1 ){
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }
    private static byte[] readInputStream(InputStream inStream, float scale, float quality) throws Exception {
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        File temp_dir = new File("");
        if (!temp_dir.exists()) {
            temp_dir.mkdirs();
        }
        String tempFileName = System.currentTimeMillis() + ".jpg";

        File image_temp = new File(temp_dir, tempFileName);
        if (!image_temp.exists()) {
            image_temp.createNewFile();
        }
        //压缩图片到临时文件
        Thumbnails.of(inStream)
                .scale(scale)
                .outputQuality(quality)
                .toFile(image_temp);

        FileInputStream is = new FileInputStream(image_temp);

        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while ((len = is.read(buffer)) != -1) {
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        is.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }

    @Test
    public void readExcel() {

        ExcelReader reader = ExcelUtil.getReader("C:\\Users\\lm_jcf\\Desktop\\pic-excel.xlsx");

        for (int i = 0; i < 1; i++) {
            List<List<Object>> read = reader.read(i);
            System.out.println(JSON.toJSONString(read));
        }
    }

    @Test
    public void sxssfWorkbookRead() throws IOException, InvalidFormatException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook(new File("C:\\Users\\lm_jcf\\Desktop\\pic-excel.xlsx")));

        SXSSFSheet sheet = workbook.getSheetAt(0);

        SXSSFRow row = sheet.getRow(3);

        SXSSFCell cell = row.getCell(row.getLastCellNum());
        System.out.println(cell.getStringCellValue());
    }

}
